package com.jiepos.mpos.common.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.entity.App_Log_Trans_JnlsEntity;
import com.jiepos.mpos.core.dao.BaseDao;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.IDFactoryUtil;
import com.jiepos.mpos.core.util.MathUtils;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * <p>
 * 标题:_交易流水信息表操作类
 * </p>
 * <p>
 * 模块: 通用平台
 * </p>
 * <p>
 * 版权: Copyright (c) 2017 江苏杰博实信息技术有限公司
 * 
 * @ProjectName: [mpos]
 * @Package com.jiepos.mpos.common.dao
 * @author 杨剑武
 * @version 1.0
 * @since 2017年10月9日下午1:48:16 @history（历次修订内容、修订人、修订时间等）
 */
@Repository
public class AppLogTransJnlsDao extends BaseDao<App_Log_Trans_JnlsEntity> {

	/**
	 * 
	 * 功能描述:新增交易流水
	 * 
	 * @param sql
	 */
	public boolean save(String sql) {
		log.info("insert sql : " + sql);
		int num = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			num = ps.executeUpdate();
			conn.commit();
			if (num > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			log.error("新增交易流水发生异常!", e);
			return false;
		} finally {
			free(conn, ps, null);
		}
	}
	
	/**
	 * 
	 * 接口: 交易结束更新交易状态
	 * @param loc_trace_no
	 * @return
	 * @since 2018年1月9日
	 */
	public boolean updateTraceNo(String loc_trace_no, String loc_dt, String loc_ts) {
		StringBuilder builder = new StringBuilder("update T_APP_LOG_TRANS_JNLS set TRS_STAT ='")
				.append(SystemConstant.RSPCODE_SUCCESS).append("' ,RSPCODE ='").append(SystemConstant.RSPCODE_XX)
				.append("',RSPMSG ='处理中', ").append("loc_dt ='").append(loc_dt).append("', loc_ts ='").append(loc_ts)
				.append("' where  loc_trace_no = '").append(loc_trace_no).append("' and ")
				.append("(loc_dt = to_char(sysdate-1,'yyyymmdd') or loc_dt = to_char(sysdate,'yyyymmdd') or loc_dt = to_char(sysdate+1,'yyyymmdd'))");
		log.info("交易结束更新交易状态sql : " + builder.toString());
		int num = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(builder.toString());
			num = ps.executeUpdate();
			conn.commit();
			if (num > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			log.error("交易结束更新交易状态发生异常!", e);
			return false;
		} finally {
			free(conn, ps, null);
		}
	}
	/**
	 * 
	 * 接口: 记录流水中短信接收记录
	 * @param loc_trace_no
	 * @return
	 * @since 2018年1月9日
	 */
	public boolean updateTraceNoIsSendRand(String loc_trace_no) {
		StringBuilder builder = new StringBuilder(
				"update T_APP_LOG_TRANS_JNLS set is_send_rand='1' where loc_trace_no = '");
		builder.append(loc_trace_no).append("' and ")
				.append("(loc_dt = to_char(sysdate-1,'yyyymmdd') or loc_dt = to_char(sysdate,'yyyymmdd') or loc_dt = to_char(sysdate+1,'yyyymmdd'))");
		log.info("update sql : " + builder.toString());
		int num = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(builder.toString());
			num = ps.executeUpdate();
			conn.commit();
			if (num > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			log.error("更新交易流水短信接收记录发生异常!", e);
			return false;
		} finally {
			free(conn, ps, null);
		}
	}
	

	/**
	 * 更新流水
	 */
	public boolean update(Map<String, String> params, Map<String, String> values) {
		Connection conn = null;
		PreparedStatement ps = null;
		int num = 0;
		try {
			StringBuffer buffer = new StringBuffer("update T_APP_LOG_TRANS_JNLS set ");
			for (Map.Entry<String, String> entry : values.entrySet()) {
				if (!StringUtils.isBlank(entry.getValue())) {
					buffer.append(entry.getKey() + " = '" + entry.getValue() + "',");
				}
			}
			buffer.deleteCharAt(buffer.length() - 1);
			buffer.append(" where ");
			for (Map.Entry<String, String> entry : params.entrySet()) {
				if (!StringUtils.isBlank(entry.getValue())) {
					buffer.append(entry.getKey() + " = '" + entry.getValue() + "' and ");
				}
			}
			if (StringUtils.isEmpty(params.get("loc_dt"))) {
				buffer.append("(loc_dt = to_char(sysdate-1,'yyyymmdd') or loc_dt = to_char(sysdate,'yyyymmdd') or loc_dt = to_char(sysdate+1,'yyyymmdd'))");
			} else {
				buffer.append("1=1");
			}
			log.info("更新流水sql=[{}]", buffer.toString());
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(buffer.toString());
			num = ps.executeUpdate();
			conn.commit();
			if (num > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			log.error("更新流水发生异常", e);
			return false;
		} finally {
			free(conn, ps, null);
		}
	}

	/**
	 * 
	 * 接口: 定时查询sql
	 * 
	 * @param loc_dt
	 * @return
	 * @author 杨剑武
	 * @since 2017年11月1日
	 */
	public List<Map<String, String>> getErrStatList() {
		List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
		StringBuilder builder = new StringBuilder();
		builder.append(
				"select t.custom_uuid, t.trf_channel_id,t.loc_trace_no,t.trans_at,t.service_ins_uuid,t.service_param_uuid,trf_mchnt_cd,trf_stl_trace_no,rspcode,loc_dt,loc_ts,complete_dt,complete_ts,")
				.append("ceil((sysdate-to_date(t.loc_dt||' '||t.loc_ts,'yyyy-MM-dd HH24:mi:ss'))*24*60) as interval ")
				.append("from t_app_log_trans_jnls t  where ")
				.append("(loc_dt = to_char(sysdate-1,'yyyymmdd') or loc_dt = to_char(sysdate,'yyyymmdd') or loc_dt = to_char(sysdate+1,'yyyymmdd')) ")
				.append("and ")
				.append("(to_date(t.loc_dt||' '||t.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-2/24 and sysdate) ")
				.append("and (t.rspcode = 'XX' or (t.rspcode = '00' and stlst = '0')) ")
				.append("and t.order_handle_st = '1' ").append("and t.trf_channel_id is not null ").append("and ")
				.append("( ")
				.append("(to_date(t.loc_dt||' '||t.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(1,'minute') and sysdate) ")
				.append("or  ")
				.append("(to_date(t.loc_dt||' '||t.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(5,'minute') and sysdate-numtodsinterval(3,'minute')) ")
				.append("or  ")
				.append("(to_date(t.loc_dt||' '||t.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(10,'minute') and sysdate-numtodsinterval(9,'minute'))  ")
				.append("or  ")
				.append("(to_date(t.loc_dt||' '||t.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(30,'minute') and sysdate-numtodsinterval(29,'minute')) ")
				.append("or ")
				.append("(to_date(t.loc_dt||' '||t.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(60,'minute') and sysdate-numtodsinterval(59,'minute')) ")
				.append("or ")
				.append("(to_date(t.loc_dt||' '||t.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(120,'minute') and sysdate-numtodsinterval(119,'minute')) ")
				.append(") ").append("for update");
		log.info("定时任务查询日志sql=[{}]", builder.toString());
		String updateSql = "update t_app_log_trans_jnls t set t.order_handle_st = '2' where t.loc_trace_no = ?";
		log.info("定时任务更新日志sql=[{}]", updateSql);
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement updatePs = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(builder.toString());
			updatePs = conn.prepareStatement(updateSql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String custom_uuid = rs.getString(1);
				String trf_channel_id = rs.getString(2);
				String loc_trace_no = rs.getString(3);
				String trans_at = rs.getString(4);
				String service_ins_uuid = rs.getString(5);
				String service_param_uuid = rs.getString(6);
				String trf_mchnt_cd = rs.getString(7);
				String trf_stl_trace_no = rs.getString(8);
				String rspcode = rs.getString(9);
				String loc_dt= rs.getString(10);
				String loc_ts= rs.getString(11);
				String complete_dt = rs.getString(12);
				String complete_ts = rs.getString(13);
				updatePs.setString(1, loc_trace_no);
				updatePs.addBatch();
				Map<String, String> dataMap = new HashMap<String, String>();
				dataMap.put("custom_uuid", custom_uuid);
				dataMap.put("trf_channel_id", trf_channel_id);
				dataMap.put("loc_trace_no", loc_trace_no);
				dataMap.put("trans_at", trans_at);
				dataMap.put("service_ins_uuid", service_ins_uuid);
				dataMap.put("service_param_uuid", service_param_uuid);
				dataMap.put("trf_mchnt_cd", trf_mchnt_cd);
				dataMap.put("trf_stl_trace_no", trf_stl_trace_no);
				dataMap.put("rspcode", rspcode);
				dataMap.put("loc_dt", loc_dt);
				dataMap.put("loc_ts", loc_ts);
				dataMap.put("complete_dt", complete_dt);
				dataMap.put("complete_ts", complete_ts);
				dataList.add(dataMap);
			}
			updatePs.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e.getMessage(), e);
			}
			log.error(e.getMessage(), e);
		} finally {
			if (updatePs != null) {
				freeStatement(updatePs);
			}
			free(conn, ps, rs);
		}
		return dataList;
	}
	
	/**
	 * 查询处理中的清算流水
	 * @return
	 */
	public List<Map<String, String>> getLogChnStlList() {
		List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
		StringBuilder builder = new StringBuilder();
		builder.append("select t1.custom_no, t1.req_sn_no,t1.loc_dt,t1.loc_ts,t1.loc_trace_no,t1.apply_amt,t1.rspcode,")
				.append("t2.trf_mchnt_cd,t2.trf_channel_id from T_APP_LOG_CHANNELSTL t1  ")
				.append(" inner join t_app_log_trans_jnls t2 on t1.loc_trace_no=t2.loc_trace_no where")
				.append(" t1.rspcode='").append(SystemConstant.RSPCODE_XX).append("'")
				.append(" and t2.rspcode='").append(SystemConstant.RSPCODE_SUCCESS).append("'")
				.append(" and t1.order_handle_st='1' and ")
				.append("(t1.loc_dt = to_char(sysdate,'yyyymmdd') or t1.loc_dt = to_char(sysdate-1,'yyyymmdd')) ")
				.append(" and ")
				.append("(to_date(t1.loc_dt||' '||t1.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-3/24 and sysdate) ")
				.append(" and ( ")
				.append("(to_date(t1.loc_dt||' '||t1.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(1,'minute') and sysdate) ")
				.append("or  ")
				.append("(to_date(t1.loc_dt||' '||t1.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(5,'minute') and sysdate-numtodsinterval(3,'minute')) ")
				.append("or  ")
				.append("(to_date(t1.loc_dt||' '||t1.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(10,'minute') and sysdate-numtodsinterval(9,'minute'))  ")
				.append("or  ")
				.append("(to_date(t1.loc_dt||' '||t1.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(30,'minute') and sysdate-numtodsinterval(29,'minute')) ")
				.append("or ")
				.append("(to_date(t1.loc_dt||' '||t1.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(60,'minute') and sysdate-numtodsinterval(59,'minute')) ")
				.append("or ")
				.append("(to_date(t1.loc_dt||' '||t1.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(120,'minute') and sysdate-numtodsinterval(119,'minute')) ")
				.append("or ")
				.append("(to_date(t1.loc_dt||' '||t1.loc_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(180,'minute') and sysdate-numtodsinterval(179,'minute')) ")
				.append(") ")
				.append(" for update");
		log.info("定时任务查询清算流水sql=[{}]", builder.toString());
		String updateSql = "update T_APP_LOG_CHANNELSTL t set t.order_handle_st = '2' where t.req_sn_no = ?";
		log.info("定时任务更新清算流水sql=[{}]", updateSql);
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement updatePs = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(builder.toString());
			updatePs = conn.prepareStatement(updateSql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String custom_no = rs.getString(1);
				String req_sn_no = rs.getString(2);
				String loc_dt = rs.getString(3);
				String loc_ts = rs.getString(4);
				String loc_trace_no = rs.getString(5);
				String apply_amt = rs.getString(6);
				String rspcode = rs.getString(7);	
				String trf_mchnt_cd = rs.getString(8);
				String trf_channel_id = rs.getString(9);
				updatePs.setString(1, req_sn_no);
				updatePs.addBatch();
				Map<String, String> dataMap = new HashMap<String, String>();
				dataMap.put("custom_no", custom_no);
				dataMap.put("req_sn_no", req_sn_no);
				dataMap.put("loc_dt", loc_dt);
				dataMap.put("loc_ts", loc_ts);
				dataMap.put("loc_trace_no", loc_trace_no);
				dataMap.put("apply_amt", apply_amt);
				dataMap.put("rspcode", rspcode);	
				dataMap.put("trf_mchnt_cd", trf_mchnt_cd);
				dataMap.put("trf_channel_id", trf_channel_id);
				dataList.add(dataMap);
			}
			updatePs.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
			log.error(e.getMessage(), e);
		} finally {
			if (updatePs != null) {
				freeStatement(updatePs);
			}
			free(conn, ps, rs);
		}
		return dataList;
	}
	
	/**
	 * 获取异步通知失败的流水
	 * @return
	 */
	public List<Map<String, String>> getNotifyFailList() {
		List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
		StringBuilder builder = new StringBuilder();
		builder.append(
				"select t.order_no,t.loc_trace_no,t.trans_at,t.midfee,t.costfee,t.custom_no,t.complete_dt,t.complete_ts,t.trs_stat,")
				.append("t.stlst,t.stl_dt,t.pay_ts,t.dto_bg_url,t.dto_page_url,t.loc_dt ")
				.append("from t_app_log_trans_jnls t  where ")
				.append(" t.complete_dt is not null and t.complete_ts is not null and ")
				.append("(complete_dt = to_char(sysdate-1,'yyyymmdd') or complete_dt = to_char(sysdate,'yyyymmdd') or complete_dt = to_char(sysdate+1,'yyyymmdd')) ")
				.append("and ")
				.append("(to_date(t.complete_dt||' '||t.complete_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-2/24 and sysdate) ")
				.append("and dto_asyc_rsp_code=99 ")
				.append("and ")
				.append("( ")				
				.append("(to_date(t.complete_dt||' '||t.complete_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(5,'minute') and sysdate-numtodsinterval(4,'minute')) ")
				.append("or  ")
				.append("(to_date(t.complete_dt||' '||t.complete_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(10,'minute') and sysdate-numtodsinterval(9,'minute'))  ")
				.append("or  ")
				.append("(to_date(t.complete_dt||' '||t.complete_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(30,'minute') and sysdate-numtodsinterval(29,'minute')) ")
				.append("or ")
				.append("(to_date(t.complete_dt||' '||t.complete_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(60,'minute') and sysdate-numtodsinterval(59,'minute')) ")
				.append("or ")
				.append("(to_date(t.complete_dt||' '||t.complete_ts,'yyyy-MM-dd HH24:mi:ss') between sysdate-numtodsinterval(120,'minute') and sysdate-numtodsinterval(119,'minute')) ")
				.append(") ");
		log.info("定时任务查询异步通知失败的流水sql=[{}]", builder.toString());		
		
		Connection conn = null;
		PreparedStatement ps = null;		
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(builder.toString());			
			rs = ps.executeQuery();
			while (rs.next()) {
				String order_no = rs.getString(1);
				String loc_trace_no = rs.getString(2);
				String trans_at = rs.getString(3);
				String midfee = rs.getString(4);
				String costfee = rs.getString(5);
				String custom_no = rs.getString(6);
				String complete_dt = rs.getString(7);
				String complete_ts = rs.getString(8);
				String trs_stat = rs.getString(9);
				String stlst= rs.getString(10);
				String stl_dt= rs.getString(11);
				String pay_ts = rs.getString(12);
				String dto_bg_url= rs.getString(13);
				String dto_page_url = rs.getString(14);
				String loc_dt = rs.getString(15);
				Map<String, String> dataMap = new HashMap<String, String>();
				dataMap.put("order_no", order_no);
				dataMap.put("loc_trace_no", loc_trace_no);
				dataMap.put("trans_at", trans_at);
				dataMap.put("midfee", midfee);
				dataMap.put("costfee", costfee);
				dataMap.put("custom_no", custom_no);
				dataMap.put("complete_dt", complete_dt);
				dataMap.put("complete_ts", complete_ts);
				dataMap.put("trs_stat", trs_stat);
				dataMap.put("stlst", stlst);
				dataMap.put("stl_dt", stl_dt);
				dataMap.put("pay_ts", pay_ts);	
				dataMap.put("dto_bg_url", dto_bg_url);
				dataMap.put("dto_page_url", dto_page_url);
				dataMap.put("loc_dt", loc_dt);
				dataList.add(dataMap);
			}			
			conn.commit();
		} catch (SQLException e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e.getMessage(), e);
			}
			log.error(e.getMessage(), e);
		} finally {			
			free(conn, ps, rs);
		}
		return dataList;
	}

	/**
	 * 异步通知更新流水 计算分润
	 * 
	 * @param map
	 * @return
	 */
	public boolean updateByNotice(Map<String, String> map) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select t1.order_no,t2.service_no,t1.loc_dt,t1.trf_channel_id,t1.trf_mchnt_cd,t1.ins_uuid,")
				.append("t1.service_param_uuid,t1.custom_uuid,t1.rspcode,t1.trans_id,t1.midfee,t1.stlst,t1.trans_at,t1.loc_trace_no,t1.complete_dt,")
				.append("t1.complete_ts,t1.stl_amt,t1.trs_stat,t1.rspmsg,t1.stl_dt,t1.trf_term_id ")
				.append(" from T_APP_LOG_TRANS_JNLS t1 inner join T_APP_SERVICE_INF t2 on t1.service_uuid=t2.service_uuid ")
				.append(" where t1.loc_trace_no='").append(map.get("loc_trace_no")).append("' for update");

		log.info("流水号=[{}]，主动查询或异步通知查询sql=[{}]", map.get("loc_trace_no"), selectSql.toString());
		StringBuffer updateSql = new StringBuffer();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(selectSql.toString());
			rs = ps.executeQuery();
			String service_no = "";
			String loc_dt = "";
			String trf_channel_id = "";
			String trf_mchnt_cd = "";
			String ins_uuid = "";
			String service_param_uuid = "";
			String custom_uuid = "";
			String rspcode = "";
			String trans_id = "";
			String midfee = "";
			String stlst = "";
			while (rs.next()) {
				service_no = rs.getString(2);
				loc_dt = rs.getString(3);
				trf_channel_id = rs.getString(4);
				trf_mchnt_cd = rs.getString(5);
				ins_uuid = rs.getString(6);
				service_param_uuid = rs.getString(7);
				custom_uuid = rs.getString(8);
				rspcode = rs.getString(9);
				trans_id = rs.getString(10);
				midfee = String.valueOf(rs.getObject(11));
				stlst = rs.getString(12);
				updateSql = new StringBuffer();
				updateSql.append("update t_app_log_trans_jnls set ");
				if (SystemConstant.RSPCODE_SUCCESS.equals(map.get("rspcode"))
						&& !SystemConstant.RSPCODE_SUCCESS.equals(rspcode)) {
					updateSql.append("trs_stat='").append(map.get("trs_stat")).append("',");
					updateSql.append("rspcode='").append(map.get("rspcode")).append("',");
					updateSql.append("rspmsg='").append(map.get("rspmsg")).append("',");
					updateSql.append("trf_trace_no='").append(map.get("trf_trace_no")).append("',");
					updateSql.append("complete_dt='").append(map.get("complete_dt")).append("',");
					updateSql.append("complete_ts='").append(map.get("complete_ts")).append("',");
					updateSql.append("stlflg='1',");
					if(!StringUtils.isBlank(String.valueOf(map.get("costfee")))){
						updateSql.append("costfee='").append(MathUtils.div(Double.parseDouble(String.valueOf(map.get("costfee"))),
								Double.parseDouble("100"), 2)).append("',");
					}
					if(!StringUtils.isBlank(String.valueOf(map.get("midfee")))){
						updateSql.append("midfee='").append(MathUtils.div(Double.parseDouble(String.valueOf(map.get("midfee"))),
								Double.parseDouble("100"), 2)).append("',");
					}
					if(!StringUtils.isBlank(String.valueOf(map.get("rcvfee")))){
						updateSql.append("rcvfee='").append(MathUtils.div(Double.parseDouble(String.valueOf(map.get("rcvfee"))),
								Double.parseDouble("100"), 2)).append("',");
					}
				}else if(SystemConstant.RSPCODE_CLOSE.equals(map.get("rspcode"))
						&& SystemConstant.RSPCODE_XX.equals(rspcode)){
					updateSql.append("trs_stat='").append(map.get("trs_stat")).append("',");
					updateSql.append("rspcode='").append(map.get("rspcode")).append("',");
					updateSql.append("rspmsg='").append(map.get("rspmsg")).append("',");
				}
				if (SystemConstant.RSPCODE_SUCCESS.equals(map.get("rspcode"))
						&&SystemConstant.AUDIT_STL_1.equals(map.get("stlst")) && !SystemConstant.AUDIT_STL_1.equals(stlst)) {
					updateSql.append("stlst='").append(SystemConstant.AUDIT_STL_1).append("',");
					updateSql.append("stl_dt='").append(map.get("stl_dt")).append("',");
					updateSql.append("loc_stl_dt='").append(map.get("stl_dt")).append("',");
					updateSql.append("stl_amt=").append(MathUtils
							.div(Double.parseDouble(String.valueOf(map.get("stl_amt"))), Double.parseDouble("100"), 2))
							.append(",");
					updateSql.append("ORDER_HANDLE_ST='3',");
					updateSql.append("pay_dt='").append(map.get("pay_dt")).append("',");
					updateSql.append("pay_ts='").append(map.get("pay_ts")).append("',");
					if(!StringUtils.isBlank(String.valueOf(map.get("trf_stl_trace_no")))){						
						updateSql.append("trf_stl_trace_no='").append(map.get("trf_stl_trace_no")).append("',");
					}
					if(!StringUtils.isBlank(String.valueOf(map.get("rcvfee")))){
						updateSql.append("rcvfee='").append(MathUtils.div(Double.parseDouble(String.valueOf(map.get("rcvfee"))),
								Double.parseDouble("100"), 2)).append("',");
					}
				}
				
				updateSql.append("trans_id=trans_id where loc_trace_no=? ");

				log.info("流水号=[{}]，主动查询或异步通知更新sql=[{}]", map.get("loc_trace_no"), updateSql.toString());
				ps = conn.prepareStatement(updateSql.toString());
				ps.setString(1, map.get("loc_trace_no"));
				ps.executeUpdate();
				if (SystemConstant.RSPCODE_SUCCESS.equals(map.get("rspcode"))
						&& SystemConstant.AUDIT_STL_1.equals(map.get("stlst"))
						&& !SystemConstant.AUDIT_STL_1.equals(stlst)) {
					map.put("updateFlag", SystemConstant.SUCCESS);
				}
				map.put("loc_dt", loc_dt);
				map.put("trf_channel_id", trf_channel_id);
				map.put("trf_mchnt_cd", trf_mchnt_cd);
				map.put("service_ins_uuid", ins_uuid);
				map.put("service_param_uuid", service_param_uuid);
				map.put("custom_uuid", custom_uuid);
				map.put("trans_id", trans_id);
				map.put("midfee", midfee);
				map.put("service_no", service_no);
			}
			flag = true;
			conn.commit();
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if(conn != null){
				conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} finally {
			free(conn, ps, rs);
		}
		return flag;
	}
	
	/**
	 * 结算失败更新流水
	 * @param map
	 * @return
	 */
	public boolean updateStlMsgFail(Map<String, String> map) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select t1.order_no,t1.loc_dt,t1.trf_channel_id,t1.trf_mchnt_cd,t1.ins_uuid,")
				.append("t1.service_param_uuid,t1.custom_uuid,t1.rspcode,t1.trans_id,t1.midfee,t1.stlst,t1.trans_at,t1.loc_trace_no,t1.complete_dt,")
				.append("t1.complete_ts,t1.stl_amt,t1.trs_stat,t1.rspmsg,t1.stl_dt,t1.trf_term_id ")
				.append(" from T_APP_LOG_TRANS_JNLS t1 ")
				.append(" where t1.loc_trace_no='").append(map.get("loc_trace_no")).append("' for update");

		log.info("流水号=[{}]，结算失败更新流水查询sql=[{}]", map.get("loc_trace_no"), selectSql.toString());
		StringBuffer updateSql = new StringBuffer();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(selectSql.toString());
			rs = ps.executeQuery();
			
			while (rs.next()) {				
				updateSql = new StringBuffer();
				updateSql.append("update t_app_log_trans_jnls set ");				
				if(SystemConstant.AUDIT_STL_2.equals(map.get("stlst"))){
					updateSql.append("stlst='").append(SystemConstant.AUDIT_STL_2).append("',");
					updateSql.append("stl_st_desc='").append(map.get("stlmsg")).append("',");
					updateSql.append("ORDER_HANDLE_ST='3',");
				}				
				updateSql.append("trans_id=trans_id where loc_trace_no=? ");

				log.info("流水号=[{}]，结算失败更新流水更新sql=[{}]", map.get("loc_trace_no"), updateSql.toString());
				ps = conn.prepareStatement(updateSql.toString());
				ps.setString(1, map.get("loc_trace_no"));
				ps.executeUpdate();				
				
			}
			flag = true;
			conn.commit();
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if(conn != null){
				conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} finally {
			free(conn, ps, rs);
		}
		return flag;
	}
	
	public boolean updateTransInfo(Map<String, String> map) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select t1.order_no,t2.service_no,t1.loc_dt,t1.trf_channel_id,t1.trf_mchnt_cd,t1.ins_uuid,")
				.append("t1.service_param_uuid,t1.custom_uuid,t1.rspcode,t1.trans_id,t1.midfee,t1.stlst,t1.trans_at,t1.loc_trace_no,t1.complete_dt,")
				.append("t1.complete_ts,t1.stl_amt,t1.trs_stat,t1.rspmsg,t1.stl_dt,t1.trf_term_id ")
				.append(" from T_APP_LOG_TRANS_JNLS t1 inner join T_APP_SERVICE_INF t2 on t1.service_uuid=t2.service_uuid ")
				.append(" where t1.loc_trace_no='").append(map.get("loc_trace_no")).append("' for update");

		log.info("流水号=[{}]，主动查询或异步通知更新支付信息查询sql=[{}]", map.get("loc_trace_no"), selectSql.toString());
		StringBuffer updateSql = new StringBuffer();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(selectSql.toString());
			rs = ps.executeQuery();			
			String rspcode = "";	
			String trf_mchnt_cd = "";
			while (rs.next()) {			
				trf_mchnt_cd = rs.getString(5);
				rspcode = rs.getString(9);				
				updateSql = new StringBuffer();
				updateSql.append("update t_app_log_trans_jnls set ");
				if (SystemConstant.RSPCODE_SUCCESS.equals(map.get("rspcode"))
						&& !SystemConstant.RSPCODE_SUCCESS.equals(rspcode)) {
					updateSql.append("trs_stat='").append(map.get("trs_stat")).append("',");
					updateSql.append("rspcode='").append(map.get("rspcode")).append("',");
					updateSql.append("rspmsg='").append(map.get("rspmsg")).append("',");
					updateSql.append("trf_trace_no='").append(map.get("trf_trace_no")).append("',");
					updateSql.append("complete_dt='").append(map.get("complete_dt")).append("',");
					updateSql.append("complete_ts='").append(map.get("complete_ts")).append("',");
					updateSql.append("stlflg='1',");
					if(!StringUtils.isBlank(String.valueOf(map.get("costfee")))){
						updateSql.append("costfee='").append(MathUtils.div(Double.parseDouble(String.valueOf(map.get("costfee"))),
								Double.parseDouble("100"), 2)).append("',");
					}
					if(!StringUtils.isBlank(String.valueOf(map.get("midfee")))){
						updateSql.append("midfee='").append(MathUtils.div(Double.parseDouble(String.valueOf(map.get("midfee"))),
								Double.parseDouble("100"), 2)).append("',");
					}
					if(!StringUtils.isBlank(String.valueOf(map.get("rcvfee")))){
						updateSql.append("rcvfee='").append(MathUtils.div(Double.parseDouble(String.valueOf(map.get("rcvfee"))),
								Double.parseDouble("100"), 2)).append("',");
					}
					map.put("withdrawFlag", SystemConstant.SUCCESS);
				}								
				updateSql.append("trans_id=trans_id where loc_trace_no=? ");

				log.info("流水号=[{}]，主动查询或异步通知更新支付信息更新sql=[{}]", map.get("loc_trace_no"), updateSql.toString());
				ps = conn.prepareStatement(updateSql.toString());
				ps.setString(1, map.get("loc_trace_no"));
				ps.executeUpdate();			
				map.put("trf_mchnt_cd", trf_mchnt_cd);
			}
			flag = true;
			conn.commit();
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if(conn != null){
				conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} finally {
			free(conn, ps, rs);
		}
		return flag;
	}
	/**
	 * 初始化渠道结算流水表
	 */
	public boolean initLogChannelStl(Map<String, String> infoMap,Map<String, String> standardMap,Map<String, String> paramMap) {
		String req_sn_no=IDFactoryUtil.getId();
		StringBuffer sb=new StringBuffer();
		sb.append("INSERT INTO ");
		sb.append(Table.T_APP_LOG_CHANNELSTL);
		sb.append(" (channeltl_uuid,custom_no,channel_no,req_sn_no,loc_dt,loc_ts,loc_trace_no,apply_amt,stlst,rspcode,rspmsg,");
		sb.append("rec_upd_usr_id,rec_crt_ts,order_handle_st)");
		sb.append(" VALUES (");		
		sb.append("'").append(IDFactoryUtil.getUUID()).append("',");//主键
		sb.append("'").append(String.valueOf(infoMap.get("custom_no"))).append("',");//会员编号
		sb.append("'").append(String.valueOf(infoMap.get("trf_channel_id"))).append("',");// 渠道编号
		sb.append("'").append(req_sn_no).append("',");//结算请求号
		sb.append("'").append(DateUtil.toString(new Date(), "yyyyMMdd")).append("',");//申请日期
		sb.append("'").append(DateUtil.toString(new Date(), "HHmmss")).append("',");//申请时间
		sb.append("'").append(String.valueOf(standardMap.get("loc_trace_no"))).append("',");//交易流水表中的本地流水号
		sb.append("'").append(String.valueOf(MathUtils.div(MathUtils.sub(Double.parseDouble(String.valueOf(standardMap.get("trans_at"))), Double.parseDouble(String.valueOf(standardMap.get("rcvfee")))), Double.parseDouble("100"), 2))).append("',");//申请金额
		sb.append("'").append(SystemConstant.AUDIT_STL_0).append("',");//清算状态
		sb.append("'").append(SystemConstant.RSPCODE_XX).append("',");//返回码
		sb.append("'").append(SystemConstant.RSPCODE_MSG_XX).append("',");//返回描述
		sb.append("'").append(String.valueOf(infoMap.get("custom_no"))).append("',");//修改的用户标识
		sb.append("sysdate").append(",");//创建时间
		sb.append("1");// 订单临时处理状态			
		sb.append(")");
		paramMap.put("req_sn_no", req_sn_no);

		log.info("流水号=[{}]，初始化渠道结算流水sql=[{}]", req_sn_no, sb.toString());
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sb.toString());
			rs = ps.executeQuery();									
			flag = true;
			conn.commit();
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if(conn != null){
				conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} finally {
			free(conn, ps, rs);
		}
		return flag;
	}
	
	/**
	 * 更新渠道结算流水
	 * @param map
	 * @return
	 */
	public boolean updateLogChannelStl(Map<String, String> map) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select custom_no,req_sn_no,loc_trace_no,rspcode")
				.append(" from T_APP_LOG_CHANNELSTL ")
				.append(" where req_sn_no='").append(map.get("req_sn_no")).append("' for update");

		log.info("流水号=[{}]，更新渠道结算流水查询sql=[{}]", map.get("req_sn_no"), selectSql.toString());
		StringBuffer updateSql = new StringBuffer();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(selectSql.toString());
			rs = ps.executeQuery();			
			
			while (rs.next()) {			
							
				updateSql = new StringBuffer();
				updateSql.append("update T_APP_LOG_CHANNELSTL set ");
				if(!StringUtils.isBlank(map.get("stlst"))&&SystemConstant.AUDIT_STL_1.equals(map.get("stlst"))){					
					updateSql.append("fee='").append(MathUtils
							.div(Double.parseDouble(String.valueOf(map.get("stlfee"))), Double.parseDouble("100"), 2)).append("',");
					updateSql.append("stl_amt='").append(MathUtils
							.div(Double.parseDouble(String.valueOf(map.get("stl_amt"))), Double.parseDouble("100"), 2)).append("',");
					updateSql.append("stlst='").append(map.get("stlst")).append("',");
					updateSql.append("stl_dt='").append(map.get("stl_dt")).append("',");
					updateSql.append("stl_ts='").append(map.get("pay_ts")).append("',");
					updateSql.append("channel_trace_no='").append(map.get("trf_stl_trace_no")).append("',");
					updateSql.append("rspcode='").append(map.get("stlcode")).append("',");
					updateSql.append("rspmsg='").append(map.get("stlmsg")).append("',");
					updateSql.append("ORDER_HANDLE_ST='3'");						
				}else if(!StringUtils.isBlank(map.get("stlst"))&&SystemConstant.AUDIT_STL_2.equals(map.get("stlst"))){
					updateSql.append("stlst='").append(map.get("stlst")).append("',");
					updateSql.append("rspcode='").append(map.get("stlcode")).append("',");
					updateSql.append("rspmsg='").append(map.get("stlmsg")).append("',");
					updateSql.append("ORDER_HANDLE_ST='3'");
				}else{
					updateSql.append("ORDER_HANDLE_ST='1'");
				}
				updateSql.append(" where req_sn_no=? ");

				log.info("流水号=[{}]，更新渠道结算流水更新sql=[{}]", map.get("req_sn_no"), updateSql.toString());
				ps = conn.prepareStatement(updateSql.toString());
				ps.setString(1, map.get("req_sn_no"));
				ps.executeUpdate();							
			}
			flag = true;
			conn.commit();
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if(conn != null){
				conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} finally {
			free(conn, ps, rs);
		}
		return flag;
	}
	
	/**
	 * 更新渠道清算流水易宝收款宝流水号
	 * @param map
	 * @return
	 */
	public boolean updateYeepaySerialno(Map<String, String> map) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select custom_no,req_sn_no,loc_trace_no,rspcode")
				.append(" from T_APP_LOG_CHANNELSTL ")
				.append(" where req_sn_no='").append(map.get("req_sn_no")).append("' for update");

		log.info("流水号=[{}]，更新渠道结算流水查询sql=[{}]", map.get("req_sn_no"), selectSql.toString());
		StringBuffer updateSql = new StringBuffer();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(selectSql.toString());
			rs = ps.executeQuery();			
			
			while (rs.next()) {										
				updateSql = new StringBuffer();
				updateSql.append("update T_APP_LOG_CHANNELSTL set ");													
				updateSql.append("yeepay_serialno='").append(map.get("yeepay_serialno")).append("'");														
				updateSql.append(" where req_sn_no=? ");

				log.info("流水号=[{}]，更新渠道结算流水更新sql=[{}]", map.get("req_sn_no"), updateSql.toString());
				ps = conn.prepareStatement(updateSql.toString());
				ps.setString(1, map.get("req_sn_no"));
				ps.executeUpdate();							
			}
			flag = true;
			conn.commit();
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if(conn != null){
				conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			flag = false;
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} finally {
			free(conn, ps, rs);
		}
		return flag;
	}

	/**
	 * 定时关闭订单
	 */
	public List<String> closeOrderRegular(String time, String trs_stat, String rspcode, String rspmsg) {
		List<String> locTraceNos = new ArrayList<String>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select order_no,loc_trace_no,loc_dt,rspcode,rspmsg from T_APP_LOG_TRANS_JNLS where ")
				.append(" (loc_dt = ''||to_char(sysdate,'yyyymmdd')||'' or loc_dt = ''||to_char(sysdate-1,'yyyymmdd')||'' ) ")
				.append(" and (to_date(loc_dt||''||loc_ts,'yyyy-MM-dd HH24:mi:ss') < (sysdate-").append(time)
				.append("/1440)) and rspcode='XX' for update");

		log.info("关闭订单查询sql=[{}]", selectSql.toString());
		StringBuffer updateSql = new StringBuffer();
		updateSql.append("update t_app_log_trans_jnls set trs_stat='").append(trs_stat).append("',rspcode='")
				.append(rspcode).append("',rspmsg='").append(rspmsg).append("' where loc_trace_no=?");

		log.info("关闭订单更新sql=[{}]", updateSql.toString());
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement updatePs = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(selectSql.toString());
			updatePs = conn.prepareStatement(updateSql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				String loc_trace_no = rs.getString(2);
				updatePs.setString(1, loc_trace_no);
				updatePs.addBatch();
				locTraceNos.add(loc_trace_no);
			}
			updatePs.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
			locTraceNos.clear();
		} catch (Exception e) {
			log.error(e.getMessage(), e);

			try {
				if(conn != null){
				conn.rollback();
				}
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
			locTraceNos.clear();
		} finally {
			free(conn, ps, rs);
		}
		return locTraceNos;
	}

	/**
	 * 
	 * 功能描述:根据条件查询指定字段的交易流水
	 * 
	 * @param custom_no
	 * @param order_no
	 * @return
	 */
	public Map<String, Object> getTranInfo(Map<String, String> params, Map<String, String> values) {
		try {
			StringBuffer buffer = new StringBuffer("select ");
			for (Map.Entry<String, String> entry : values.entrySet()) {
				buffer.append(entry.getValue() + ",");
			}
			buffer.deleteCharAt(buffer.length() - 1);
			buffer.append(" from ").append(Table.T_APP_LOG_TRANS_JNLS).append(" where ");
			for (Map.Entry<String, String> entry : params.entrySet()) {
				if (!StringUtils.isBlank(entry.getValue())) {
					buffer.append(entry.getKey() + "='" + entry.getValue() + "' and ");
				}
			}
			if (StringUtils.isEmpty(params.get("loc_dt"))) {
				buffer.append("(loc_dt = to_char(sysdate-1,'yyyymmdd') or loc_dt = to_char(sysdate,'yyyymmdd') or loc_dt = to_char(sysdate+1,'yyyymmdd'))");
			} else {
				buffer.append("1=1");
			}
			buffer.append(" order by loc_ts desc");
			log.info("查询流水sql=[{}]", buffer.toString());
			List<Map<String, Object>> list = jdbcTemplate.queryForList(buffer.toString());
			if (list != null && !list.isEmpty()) {
				return list.get(0);
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}

	/**
	 * 
	 * 接口: 检查系统跟踪号是否重复
	 * 
	 * @param custom_no
	 * @param snd_trace_no
	 * @return
	 * @throws SQLException
	 * @since 2017年11月21日
	 */
	public boolean getSndTraceNo(String custom_no, String snd_trace_no, String loc_dt) throws SQLException {
		StringBuilder builder = new StringBuilder("select loc_dt,loc_trace_no from ").append(Table.T_APP_LOG_TRANS_JNLS)
				.append(" where loc_dt = to_char(sysdate,'yyyymmdd') and").append(" custom_no='").append(custom_no)
				.append("' and snd_trace_no='").append(snd_trace_no).append("' and loc_dt ='").append(loc_dt)
				.append("'");
		log.info("检查系统跟踪号是否重复sql=[{}]", builder.toString());
		List<Map<String, Object>> list = jdbcTemplate.queryForList(builder.toString());
		if (list != null && !list.isEmpty()) {
			return true;
		}
		return false;
	}

	/**
	 * 建立数据库连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {

		return jdbcTemplate.getDataSource().getConnection();
	}

	/**
	 * 释放连接
	 * 
	 * @param conn
	 */
	private static void freeConnection(Connection conn) {
		try {
			conn.close();
			conn = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 释放PreparedStatement
	 * 
	 * @param statement
	 */
	private static void freeStatement(PreparedStatement statement) {
		try {
			statement.close();
			statement = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 释放PreparedStatement
	 * 
	 * @param statement
	 */
	private static void freeResultSet(ResultSet set) {
		try {
			set.close();
			set = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 释放资源
	 * 
	 * @param conn
	 * @param statement
	 * @param rs
	 */
	private void free(Connection conn, PreparedStatement statement, ResultSet set) {
		if (set != null) {
			freeResultSet(set);
		}
		if (statement != null) {
			freeStatement(statement);
		}
		if (conn != null) {
			freeConnection(conn);
		}
		// System.gc();
	}

}
